package it.ap.sa.core.dao.util;

import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import it.ap.sa.core.dao.generator.ICriteriaGenerator;

public class AbstractGenericEntityDao<T> {
	
	private static final Log logger = LogFactory.getLog(AbstractGenericEntityDao.class.getName());
	
	@Autowired
	private SessionFactory sessionFactory;
	
	protected SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	protected void setSessionFct(SessionFactory sessionFactory){
		this.sessionFactory = sessionFactory;
	}
	///////////////////////////////////////////////
	//        BASE OPERATION
	///////////////////////////////////////////////
	
	public void saveOrUpdateEntity(T t) throws Exception{
		try {
			this.sessionFactory.getCurrentSession().saveOrUpdate(t);
		} catch (Exception e) {
			logger.fatal("Errore durante l'inserimento dell'entita' "+t, e);
			throw e;
		}
	}
	
	public void saveEntity(T t) throws Exception{
		try {
			this.sessionFactory.getCurrentSession().save(t);
		} catch (Exception e) {
			logger.fatal("Errore durante l'inserimento dell'entita' "+t, e);
			throw e;
		}
	}
	
	public void flush() throws Exception{
		try {
			this.sessionFactory.getCurrentSession().flush();
		} catch (Exception e) {
			logger.fatal("Errore durante flush", e);
			throw e;
		}
	}
	
	public void refresh(T t) throws Exception{
		try {
			this.sessionFactory.getCurrentSession().refresh(t);
		} catch (Exception e) {
			logger.fatal("Errore durante refresh", e);
			throw e;
		}
	}
	
	public void updateEntity(T t) throws Exception{
		try {
			this.sessionFactory.getCurrentSession().update(t);
		} catch (Exception e) {
			logger.fatal("Errore durante l'aggiornamento dell'entita' "+t, e);
			throw e;
		}
	}
	
	public void deleteEntity(T t) throws Exception{
		try {
			this.sessionFactory.getCurrentSession().delete(t);
		} catch (Exception e) {
			logger.fatal("Errore durante l'inserimento dell'entita' "+t, e);
			throw e;
		}
	}
	
	/**
	 * cerca le entity in base al criterio passato
	 * @param criteria
	 * @param skip
	 * @param maxNumber
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public List<T> findEntities(DetachedCriteria criteria, int skip, int maxNumber, List<Order> orders) throws Exception{
		try {
			Session sessione = this.sessionFactory.getCurrentSession();
			Criteria cri = null;
			if(criteria != null){
				cri = criteria.getExecutableCriteria(sessione);
			}
			cri.setFirstResult(skip);
			cri.setMaxResults(maxNumber);
			if(orders != null && !orders.isEmpty()){
				for (Order order : orders) {
					cri.addOrder(order);
				}
			}
			return cri.list();
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entità", e);
			throw e;
		}
	}
	
	/**
	 * cerca le entity in base al criterio passato
	 * @param criteria
	 * @param skip
	 * @param maxNumber
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> findEntities2(DetachedCriteria criteria, int skip, int maxNumber, List<Order> orders) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			Session sessione = this.sessionFactory.getCurrentSession();
			Criteria cri = null;
			if(criteria == null){
				throw new Exception("null criteria");
			}
			cri = criteria.getExecutableCriteria(sessione);
			//count Entities
			ret.setTotalrecord(countEntities(cri));
			cri = criteria.getExecutableCriteria(sessione);
			cri.setProjection(null);
			cri.setResultTransformer(Criteria.ROOT_ENTITY);
			cri.setFirstResult(skip);
			cri.setMaxResults(maxNumber);
			if(orders != null && !orders.isEmpty()){
				for (Order order : orders) {
					cri.addOrder(order);
				}
			}
			ret.setData(cri.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita'", e);
			throw e;
		}
	}
	
	@Transactional(readOnly=true)
	public long countEntities(Criteria criteria) throws Exception{
		try { 
			Long result = 0L;
			if( criteria != null && 
					criteria.setProjection(Projections.rowCount()) != null &&
					criteria.setProjection(Projections.rowCount()).uniqueResult() != null ){
				
				 result = (Long)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}
			return result;
		} catch (Exception e) {			
			logger.fatal("Errore durante la ricerca delle entita ", e);
			throw e;
		}
	}
	 
	@Transactional(readOnly=true)
	public long countEntities(DetachedCriteria criteria) throws Exception{
		try {
			Long result = 0L;
			Session sessione = this.sessionFactory.getCurrentSession();
			Criteria cri = null;
			if(criteria != null){
				cri = criteria.getExecutableCriteria(sessione);
				result = (Long)cri.setProjection(Projections.rowCount()).uniqueResult();
			} 
			return result;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita'", e);
			throw e;
		}
	}
	
	/**
	 * 
	 * @param theId
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public T findById(Serializable theId,T obj ) throws Exception{
		return findById(theId,  false, obj,null);
	}
	
	/**
	 * carica una entity dalla chiave primaria
	 * @param theId
	 * @param avoidLazy
	 * @param obj
	 * @param propertyNamesToInitialize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public T findById(Serializable theId, boolean avoidLazy, T obj, List<String> propertyNamesToInitialize) throws Exception{
		try {
			Session sessione = this.sessionFactory.getCurrentSession();
			T result = (T) sessione.get(obj.getClass(), theId);
			if(avoidLazy){
				if( propertyNamesToInitialize == null || propertyNamesToInitialize.isEmpty() ){
					throw new IllegalArgumentException("Impossibile inizializzare con una lista di nomi di proprietà nulla o vuota");
				}
				
				for (String propertyName : propertyNamesToInitialize) {
					Hibernate.initialize(PropertyUtils.getProperty(obj, propertyName));
				}
			}
			return result;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita'", e);
			throw e;
		}
	}
	
	/**
	 * cerca le entity a partire da un criterio passato
	 * @param criteria
	 * @param skip
	 * @param maxNumber
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> findEntitiesByCriteria(Criteria criteria, int skip, int maxNumber, List<Order> orders) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			//count Entities
			ret.setTotalrecord(countEntities(criteria));
			//annullo e ricreo per la lista paginata 
			//TODO (verificare se funziona sempre)
			criteria.setProjection(null);
			criteria.setResultTransformer(Criteria.ROOT_ENTITY);
			criteria.setFirstResult(skip);
			criteria.setMaxResults(maxNumber);
			if(orders != null && !orders.isEmpty()){
				for (Order order : orders) {
					criteria.addOrder(order);
				}
			}
			ret.setData(criteria.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita' ", e);
			throw e;
		}
	}
	
	/**
	 * cerca le entity a partire da un criterio passato
	 * @param criteria
	 * @param skip
	 * @param maxNumber
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> findEntitiesByCriteria(Criteria criteria, int skip, int maxNumber, List<Order> orders, ResultTransformer resultTransformer, Projection proj) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			//count Entities
			ret.setTotalrecord(countEntities(criteria));
			//annullo e ricreo per la lista paginata 
			//TODO (verificare se funziona sempre)
			criteria.setProjection(proj);
			criteria.setResultTransformer(resultTransformer);
			criteria.setFirstResult(skip);
			criteria.setMaxResults(maxNumber);
			if(orders != null && !orders.isEmpty()){
				for (Order order : orders) {
					criteria.addOrder(order);
				}
			}
			ret.setData(criteria.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita' ", e);
			throw e;
		}
	}
	
	/**
	 * cerca le entity a partire da un criterio passato
	 * @param criteria
	 * @param skip
	 * @param maxNumber
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> findEntitiesByCriteria(Criteria criteria, int startIndex, int endIndex) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			Long totRecord = countEntities(criteria);
			ret.setTotalrecord(totRecord);
			
			criteria.setFirstResult(startIndex);
			criteria.setMaxResults(endIndex);
			criteria.setProjection(null);
			criteria.setResultTransformer(Criteria.ROOT_ENTITY);
			ret.setData(criteria.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita ", e);
			throw e;
		}
	}
	
	//search with generator
	/**
	 * cerca in una tabella con un generator {@link ICriteriaGenerator}
	 * @param generator
	 * @param recForPage
	 * @param fromRec
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public TPagingList<T> findWithGenerator(final ICriteriaGenerator generator,final int recForPage,final int fromRec)throws Exception{
		 
		final TPagingList<T> ret = new TPagingList<T>();
		Session sessione = this.sessionFactory.getCurrentSession();
		 
		Criteria crit = generator.build(sessione);
		logger.debug("finding with this criteria:"+crit.toString());

		Long tot = countEntities(crit);
		ret.setTotalrecord(tot);
		//ricostruisco il criterio per la lista

		crit = generator.build(sessione);
		crit = generator.addOrders(crit);

		 logger.debug("cerco con il criterio:"+crit);

		if(recForPage != -1 && fromRec != -1){
			ret.setData(crit.setFirstResult(fromRec).setMaxResults(recForPage).list());
		}else{
			ret.setData(crit.list());
		}
		return ret;
	}
	
	/**
	 * cerca le entity a partire da un oggetto filtro F
	 * @param <F>
	 * @param filter
	 * @param start
	 * @param recForPage
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	public <F> TPagingList<T> findByFilter(F filter, int start, int recForPage, List<Order> orders) throws Exception{
		Session session = getSessionFactory().getCurrentSession();
		//costruisci il criterio dipende dal dao specifico
		Criteria crit = this.buildSearchCriteria(session, filter, start, recForPage,orders);
		return findEntitiesByCriteria(crit, start, recForPage, orders);
	}
	
	/**
	 * 
	 * @param session
	 * @param filter
	 * @param start
	 * @param recForPage
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	public <F> Criteria buildSearchCriteria(Session session, F filter, int start, int recForPage, List<Order> orders)throws Exception{
		throw new Exception("implement in sublcass");
	}
	
	/**
	 * cerca le entity a partire da un criterio passato
	 * @param criteria
	 * @param skip
	 * @param maxNumber
	 * @param orders
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> find(Criteria criteria, int startIndex, int endIndex) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			Long totalRecord = new Long(countEntities(criteria));
			ret.setTotalrecord(totalRecord);
			criteria.setFirstResult(startIndex);
			criteria.setMaxResults(endIndex);
			criteria.setProjection(null);
			criteria.setResultTransformer(Criteria.ROOT_ENTITY);
			ret.setData(criteria.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entità ", e);
			throw e;
		}
	}
	
	/**
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> find(Criteria criteria) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			criteria.setFirstResult(0);
			criteria.setResultTransformer(Criteria.ROOT_ENTITY);
			ret.setData(criteria.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita ", e);
			throw e;
		}
	}
	
	/**
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public TPagingList<T> find(Criteria criteria, List<Order> orders) throws Exception {
		try {
			TPagingList<T> ret = new TPagingList<T>();
			criteria.setFirstResult(0);
			criteria.setResultTransformer(Criteria.ROOT_ENTITY);
			
			if (orders != null && !orders.isEmpty()) {
				for (Order order : orders) {
					criteria.addOrder(order);
				}
			}

			ret.setData(criteria.list());

			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita ", e);
			throw e;
		}
	}
	
	/**
	 * @param session
	 * @param sql
	 * @param transformers
	 * @param scalar
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> findSqlQuery(Session session, String sql,  ResultTransformer transformers, Map<String, Type> scalar) throws Exception{
		try {
			TPagingList<T> ret = new TPagingList<T>();
			SQLQuery query = session.createSQLQuery(sql);
			/** set scalar **/
			if(null != scalar){
				for (String alias : scalar.keySet()) {
					query.addScalar(alias, scalar.get(alias));
				}
			}
			/** set transformer**/
			query.setResultTransformer(transformers);
			ret.setData(query.list());
			return ret;
		} catch (Exception e) {
			logger.fatal("Errore durante la ricerca delle entita' ", e);
			throw e;
		}
	}
	
	/**
	 * Esegue la query sql data in ingresso in maniera paginata
	 *
	 * @param session - hibernate session
	 * @param sql - query da eseguire
	 * @param transformers - result transformer
	 * @param scalar - mappa di scalar per inserire la tipologia dei campi restituiti
	 * @param {@link Map<String, Object>} paramSql, mappa dei parametri da sostituire nella query sql (key: nome parametro, value: valore da ricercare ).
	 * @param firstResult - intero rappresentante la posizione del primo risultato da recuperare
	 * @param maxNumber - intero rappresentante il numero totale di elementi da recuperare
	 * @return lista paginata di elementi
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Transactional(readOnly=true)
	public TPagingList<T> findSqlQuery(Session session, String sql, ResultTransformer transformers, Map<String, Type> scalar, Map<String, Object> paramSql, Integer firstResult, Integer maxNumber) throws Exception{
		try {
	         TPagingList<T> ret = new TPagingList<T>();
	         SQLQuery query = session.createSQLQuery(sql);

	         /** set scalar **/
	         if(null != scalar){
	        	 for (String alias : scalar.keySet()) {
	        		 query.addScalar(alias, scalar.get(alias));
	             }
	         }

	         /*
	          * Bind the values of the given Map for each named parameters of the query,
	          */
	         query.setProperties(paramSql);
	           
	         /** pagina la query **/
	         query.setFirstResult(firstResult);
	         query.setMaxResults(maxNumber);
	         
	         /** set transformer**/
	         query.setResultTransformer(transformers);

	         ret.setData(query.list());

	         return ret;
		} catch (Exception e) {
			logger.fatal("findSqlQuery >>>>>>>>>>> ", e);
			throw e;
		}
	}
	
	/**
	 * Esegue la query sql data in ingresso e ritorna il numero di righe.
	 * La query sql deve esse impostata nel seguente modo: SELECT COUNT(..) as count_row FROM ...
	 * La count deve avere l'alias uguale a "count_row".
	 * 
	 * @param {@link Session} session - hibernate session
	 * @param {@link String} sql - query da eseguire
 	 * @param {@link ResultTransformer} transformers - result transformer
	 * @param {@link Map<String, Type>} scalar - mappa di scalar per inserire la tipologia dei campi restituiti
	 * @param {@link Map<String, Object>} paramSql, mappa dei parametri da sostituire nella query sql (key: nome parametro, value: valore da ricercare ).
	 * 
	 * @return numero di righe recuperate dalla query
	 * @throws Exception
	 */
	@Transactional(readOnly=true)
	public static Long countSqlEntities(Session session, String sql, ResultTransformer transformers, Map<String, Object> paramSql) throws Exception{
		try { 
			SQLQuery query = session.createSQLQuery(sql);
			
			/*
			 * Bind the values of the given Map for each named parameters of the query,
			 */
			query.setProperties(paramSql);
			/*
			 * Set a strategy for handling the query results. This can be used to change
			 * "shape" of the query result.
			 */
			query.setResultTransformer(transformers);
			/* */
			query.addScalar("count_row", StandardBasicTypes.LONG);
			
			Long rowCount = (Long) query.uniqueResult();
			
			return rowCount;
		} catch (Exception e) {			
			logger.fatal(" countSqlEntities >>>>>>>>>>>>>>< ", e);
			throw e;
		}
	}
	/**
	 * @author Alessandro Paolillo
	 */
	protected SQLQuery buildSQLFromBean(ExporterSqlQueryBean sqlQueryBean){
//Creazione sql		
		SQLQuery query = this.sessionFactory.getCurrentSession().createSQLQuery(sqlQueryBean.getSql());
		
//Inserimento parametri		
		if(sqlQueryBean.getParameterNames() != null){
			for(Entry<String, Object> parameter : sqlQueryBean.getParameterNames().entrySet()){
				if(parameter instanceof Collection){
					query.setParameterList(parameter.getKey(), (Collection<?>)parameter.getValue());
				}else{
					query.setParameter(parameter.getKey(), parameter.getValue());
				}
			}
			
		}
		
//Set output		
		for(Entry<String, Type> fieldsOutput : sqlQueryBean.getMapFieldsOutput().entrySet()){
			query.addScalar(fieldsOutput.getKey(), fieldsOutput.getValue());
		}
		
		query.setResultTransformer(new AliasToBeanResultTransformer(sqlQueryBean.getBeanResult()));
		
		return query;
	}
	
	/**
	 * Gestione del Sequence
	 */
	@SuppressWarnings("unchecked")
	@Transactional
	public Long getNextValueSequence() throws Exception {
		try {
			Long nextValue = ((Number) getSessionFactory().getCurrentSession()
					.createSQLQuery("select nextval('sequence_id')")
					.uniqueResult()).longValue();
			return nextValue;
		} catch (Exception e) {
			throw (e);
		}
	}
	
	protected SQLQuery createSQLQuery(String sql){
		return this.sessionFactory.getCurrentSession().createSQLQuery(sql);
	}
}
